#!/bin/bash
#声明当前脚本使用bash解释器质性
#接收外部传入的日期参数，如果外部没有传入日期参数，怎么办？
#严格:外部没有传入，就退出 exit  非严格：如果外部没有传入，自己生成一个默认的日期(当前机器时间的昨天)
#-n代表判断字符串的长度，如果字符串长度为0，返回false,否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
	do_date= $(date -d yesterday +%F)
fi

echo 当前要操作的日期是$do_date

dwd_trade_pay_detail_suc_inc="
    set hive.exec.dynamic.partition.mode=nonstrict;
with oi as (
    select
           data.user_id,
           data.province_id,
           data.id
    from ods_order_info_inc
    where dt = $do_date
),
     od as (
         select
                data.id,
                data.order_id,
                data.course_id,
                data.course_name course_name,
                data.origin_amount,
                data.coupon_reduce,
                data.final_amount
         from ods_order_detail_inc
         where dt = $do_date and type='bootstrap-insert'
     ),
     py as (
         select
                data.id,
                data.order_id,
                data.payment_type,
                data.payment_status,
                data.callback_time
         from ods_payment_info_inc
         where dt = $do_date  and type='insert'
         and data.callback_time is not null
     )
insert overwrite table dwd_trade_pay_detail_suc_inc partition(dt=$do_date)
select
       py.id id,
       oi.id order_id,
       oi.user_id user_id,
       od.course_id course_id,
       od.course_name course_name,
       oi.province_id province_id,
       py.payment_type payment_type,
       py.payment_status payment_status,
       date_format(py.callback_time, 'yyyy-MM-dd') date_id,
       py.callback_time callback_time,
       od.origin_amount origin_amount,
       od.coupon_reduce coupon_reduce,
       od.final_amount final_amount
from py
         left join od
                   on py.order_id=od.order_id
         left join oi
                   on py.order_id = oi.id;
"
dwd_traffic_action_inc="
    set hive.cbo.enable=false;
insert overwrite table dwd_traffic_action_inc partition(dt=$do_date)
select
    province_id,     --'
    brand,          --'手机品牌'
    channel,         --'渠道'
    is_new,          --'是否首次启动'
    model,          --'手机型号'
    mid_id,          --'设备id'
    operate_system,  --'操作系统'
    sc,              --来源
    sid,            --会话id
    user_id,         --'会员id'
    version_code,    --'app版本号'
    during_time,     --'持续时间毫秒'
    page_item,      --'目标id '
    page_item_type,  --'目标类型'
    last_page_id,    --'上页类型'
    page_id,         --'页面id '
    action_id,       --'动作id'
    action_item,     --'目标id '
    action_item_type,    --'目标类型'
    date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd') date_id,         --'日期id'
    date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') action_time     --'动作发生时间'
	from(

		select
			common.ar province_id,  --
			common.ba brand,        --
			common.ch channel,
			common.is_new is_new,
			common.md model,
			common.mid mid_id,
			common.os operate_system,
		    common.sc ,
		    common.sid ,
			common.uid user_id,
			common.vc version_code,
			page.during_time,
			page.item page_item,
			page.item_type page_item_type,
			page.last_page_id,
			page.page_id,
			action.action_id,
			action.item action_item,
			action.item_type action_item_type,
			action.ts
		from ods_log_inc lateral view explode(actions) tmp as action
		where dt = $do_date and actions is not null
	)log;
"
dwd_paly_video_inc="
    with log as (
    select
			common.uid ,
           common.sid,
           appvideo.play_sec,
           appvideo.position_sec,
           appvideo.video_id,
           date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd') play_time
    from ods_log_inc
    where dt=$do_date and appvideo.play_sec is not null
    ),ucp as (
        select
            data.id,
               data.course_id,
               data.chapter_id,
               data.user_id,
               data.position_sec,
               data.create_time
        from ods_user_chapter_process_inc
        where dt = $do_date
)
insert overwrite  table dwd_paly_video_inc partition (dt=$do_date)
select
    log.uid user_id,
       log.play_sec,
       log.position_sec,
       log.video_id,
       log.sid,
       log.play_time,
       ucp.course_id,
       ucp.chapter_id
from log left join ucp
    on log.uid=ucp.user_id;
"
dwd_paly_course_inc="
    with vi as (
    select
        id,
           video_name,
           during_sec,
           video_status,
           version_id,
           chapter_id,
           course_id
    from ods_video_info_full
    where dt=$do_date and  video_status = '1'
),ucp as (
    select
        data.id,
           data.course_id,
           data.chapter_id,
           data.user_id,
           data.position_sec,
           date_format(data.create_time,'yyyy-MM-dd') create_time
    from ods_user_chapter_process_inc
    where dt=$do_date and type = 'bootstrap-insert'
),log as (
    select
		   common.uid ,
           common.sid,
           sum(appvideo.play_sec) play_sec,
           appvideo.video_id
    from ods_log_inc
    where dt=$do_date and appvideo.play_sec is not null
    group by common.uid, common.sid,appvideo.video_id

)
insert overwrite  table dwd_paly_course_inc partition(dt=$do_date)
select
    ucp.id id,
       ucp.course_id,
       ucp.chapter_id,
       ucp.user_id,
       ucp.create_time,
       ucp.position_sec,
       vi.during_sec,
       log.play_sec,
       log.sid,
       vi.id video_id,
       vi.video_name,
       vi.video_status,
       vi.version_id

from ucp left join vi
    on ucp.course_id=vi.course_id
    and ucp.chapter_id=vi.chapter_id
left join log
    on ucp.user_id=log.uid
    and vi.id =log.video_id;
"
dwd_trade_order_detail_inc"
insert overwrite table dwd_trade_order_detail_inc partition (dt = $do_date)
select
        a.order_id,
        a.user_id,
        a.course_id,
        a.course_name,
        a.create_time,
        a.origin_amount,
        a.coupon_reduce,
        a.final_amount,
        c.province_name
from
(
    select data.id id,
           data.order_id order_id
,           data.user_id user_id,
           data.course_id course_id,
           data.course_name course_name,
           data.create_time create_time,
           data.origin_amount origin_amount,
           data.coupon_reduce coupon_reduce,
           data.final_amount final_amount
    from ods_order_detail_inc
    where dt = $do_date
) a
left join
(
    select data.id id,
           data.province_id province_id
    from ods_order_info_inc
   where dt = $do_date
) b
on a.order_id = b.id
left join
(
    select id,
           name province_name
    from ods_base_province_full
    where dt = '$do_date'
    ) c
on b.province_id = c.id;
"


dwd_traffic_start_inc = "
set hive.cbo.enable=false;
insert overwrite table dwd_traffic_start_inc partition(dt='$do_date')
select
        province_id,
       brand,
       channel,
       is_new,
       model,
       mid_id,
       operate_system,
       source,
       session,
       user_id,
       version_code,
       entry,
       first_open,
       loading_time,
       open_ad_id,
       open_ad_ms,
       open_ad_skip_ms
from
(
    select common.ar province_id_1,---------
           common.ba brand,
           common.ch channel ,
           common.is_new is_new,
           common.md model,
           common.mid mid_id,
           common.sc source_1,---------
           common.os operate_system,
           common.sid session,
           common.uid user_id,
           common.vc version_code,
           `start`.entry entry,
           `start`.first_open first_open,
           `start`.loading_time loading_time,
           `start`.open_ad_id open_ad_id,
           `start`.open_ad_ms open_ad_ms,
           `start`.open_ad_skip_ms open_ad_skip_ms
    from ods_log_inc
    where dt = '$do_date'
      and `start` is not null
) log
left join
(
select
        id,
        name province_id
from ods_base_province_full where dt = '$do_date'
) bp
on log.province_id_1 = bp.id
left join
(
select
        id,
        source_site source
from ods_base_source_full where dt = '$do_date'
) bs
on log.source_1 = bs.id;
"


dwd_traffic_page_view_inc = "
with log as (
        select
        common.ar ar,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.sc source_type,
        common.sid,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ods_log_inc
    where dt='2022-07-04'
    and page is not null
),bp as (
         -- 获取省份信息
    select
            id province_id,
           -- area_code 和 省份id是 1:1
           area_code
    from
    ods_base_province_full
    where dt='2022-07-04'
)
insert overwrite table dwd_traffic_page_view_inc partition (dt='2022-07-04')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    source_type,
    sid,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from log left join bp on log.ar = bp.province_id;
"


dwd_traffic_start_inc = "
insert overwrite table dwd_user_login_inc partition (dt='2022-07-04')
select
       user_id,
       date_id,
       view_time login_time,
       channel,
       province_id,
       version_code,
       mid_id,
       brand,
       model,
       operate_system
    from (
         select user_id,
                date_id,
                channel,
                province_id,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                view_time,
                -- 每个会话中第一次
                row_number() over (partition by session_id order by view_time ) rn
         from dwd_traffic_page_view_inc
         where dt = '2022-07-04'
           -- 过滤得到所有 userid 不为null的页面访问
           and isnotnull(user_id)
             ) t1
where rn = 1;
"

dwd_interaction_review_info_inc = "
with ri as (
    select
            data.id,
            data.user_id,
            data.course_id,
            data.review_txt,
            data.review_stars,
            data.create_time
    from ods_review_info_inc
    where dt = '2022-07-04'
),ci as (
    select
           id,
           course_name,
           subject_id
    from ods_course_info_full
    where dt = '2022-07-04'
),ui as (
    select
           data.id,
           data.login_name
    from ods_user_info_inc
    where dt = '2022-07-04'
)

insert overwrite table dwd_interaction_review_info_inc partition (dt ='2022-07-04')

select
        ri.id,
        user_id,
        login_name,
        course_id,
        course_name,
        subject_id,
        review_txt,
        review_stars,
        create_time
from ri left join ci on ri.course_id = ci.id
left join ui on ri.user_id = ui.id;
"



dwd_traffic_err_inc = "
insert overwrite table dwd_traffic_err_inc partition(dt='2022-07-04')
select
       province_id,
       brand,
       channel,
       is_new,
       model,
       mid_id,
       operate_system,
       source,
       session,
       user_id,
       version_code,
       error_code,
       msg
from
(
    select common.ar province_id_1,---------
           common.ba brand,
           common.ch channel ,
           common.is_new is_new,
           common.md model,
           common.mid mid_id,
           common.sc source_1,---------
           common.os operate_system,
           common.sid session,
           common.uid user_id,
           common.vc version_code,
           err.error_code,
           err.msg
    from ods_log_inc
    where dt = '2022-07-04'
    and isnotnull(err)
) log
left join
(
select
        id,
        name province_id
from ods_base_province_full where dt = '2022-07-04'
) bp
on log.province_id_1 = bp.id
left join
(
select
        id,
        source_site source
from ods_base_source_full where dt = '2022-07-04'
) bs
on log.source_1 = bs.id;
"

dwd_trade_cart_add_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cart_add_inc partition (dt)
select
    data.id,
    data.user_id,
    data.course_id,
    data.course_name,
    date_format(nvl(data.update_time,data.create_time),'yyyy-MM-dd') date_id,
    data.create_time,
    data.session_id,
    date_format(nvl(data.update_time,data.create_time),'yyyy-MM-dd') date_id
from gmall.ods_cart_info_inc
where dt='$do_date' and type = 'insert';
"
dwd_traffic_display_inc="
with td as (
    select
        common.ar,
        common.ba,
        common.ch,
        common.is_new,
        common.md,
        common.mid,
        common.os,
        common.uid,
        common.vc,
        page.during_time,
        page.item,
        page.item_type,
        page.last_page_id,
        page.page_id,
        common.sc source_id,
        display.display_type,
        display.item display_item,
        display.item_type display_item_type,
           -- order 会和 sql中的order冲突
        display.`order` display_order,
        display.pos_id,
           ts
    from ods_log_inc
    lateral view explode(displays) tmp as display
    where dt='$do_date'
      -- 过滤曝光数据
    and size(displays) > 0
),bp as (
   -- 获取省份信息
    select
            id province_id,
           -- area_code 和 省份id是 1:1
           area_code
    from
    ods_base_province_full
    where dt='$do_date'
),sc as (
    -- 获取来源类型
    select
            id source_id,
           source_site source_type
    from ods_base_source_full
    where dt='$do_date'
)
insert overwrite table dwd_traffic_display_inc partition (dt='$do_date')
select
       area_code,
       ba brand,
       ch channel,
       is_new,
       md model,
       mid mid_id,
       os operate_system,
       uid user_id,
       vc version_code,
       during_time,
      item page_item,
      item_type page_item_type,
       last_page_id,
       page_id,
       td.source_id,
       source_type,
       '$do_date' date_id,
      date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss')  display_time,
       display_type,
       display_item,
       display_item_type,
       display_order,
       pos_id display_pos_id
from td
left join bp on td.ar=bp.province_id
left join sc on td.source_id=sc.source_id;
"
dwd_examine_test_exam_inc="
--首日
with exam as (
    select
        data.id exam_id,
        data.paper_id,
        data.user_id,
        data.score,
        data.duration_sec,
        data.create_time,
        data.submit_time,
        data.update_time,
        date_format(from_utc_timestamp(ts*1000,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') date_id
    from ods_test_exam_inc
    where dt='$do_date' and type='bootstrap-insert'
),us as (
    select
        data.id,
        data.real_name user_name
    from ods_user_info_inc
    where dt='$do_date' and type='bootstrap-insert'
),paper as (
    select
        id paper_id,
        paper_title paper_name,
        course_id
    from ods_test_paper_full
    where dt='$do_date'
),course as (
    select
        id course_id,
        course_name
    from ods_course_info_full
    where dt='$do_date'
),question as (
    select
        data.id question_id,
        data.score question_score,
        data.paper_id
    from ods_test_exam_question_inc
    where dt='$do_date' and type='bootstrap-insert'
)
insert overwrite table dwd_examine_test_exam_inc partition (dt='$do_date')
select
       exam_id,
       user_id,
       user_name,
       paper.course_id,
       course_name,
       exam.paper_id,
       paper_name,
       score,
       date_id,
       create_time,
       duration_sec,
       submit_time,
       update_time,
       question_id,
       question_score
from exam
left join us on user_id=us.id
left join paper on exam.paper_id=paper.paper_id
left join course on paper.course_id=course.course_id
join question on exam.paper_id = question.paper_id;
"
case $2 in
"dwd_trade_pay_detail_suc_inc")
	hive --database gmall -e "$dwd_trade_pay_detail_suc_inc"
	;;
"dwd_traffic_action_inc")
	hive --database gmall -e "$dwd_traffic_action_inc"
	;;
"dwd_paly_video_inc")
	hive --database gmall -e "$dwd_paly_video_inc"
	;;
"dwd_paly_course_inc")
	hive --database gmall -e "$dwd_paly_course_inc"
	;;
"dwd_trade_order_detail_inc")
	hive --database gmall -e "$dwd_trade_order_detail_inc"
	;;
"dwd_traffic_start_inc")
	hive --database gmall -e "$dwd_traffic_start_inc"
	;;
"dwd_traffic_page_view_inc")
	hive --database gmall -e "$dwd_traffic_page_view_inc"
	;;
"dwd_user_login_inc")
	hive --database gmall -e "dwd_user_login_incc"
	;;
 "dwd_interaction_review_info_inc")
	hive --database gmall -e "dwd_interaction_review_info_inc"
	;;
 "dwd_traffic_err_inc")
	hive --database gmall -e "dwd_traffic_err_inc"
	;;
 "dwd_trade_cart_add_inc")
	hive --database gmall -e "dwd_trade_cart_add_inc"
	;;
 "dwd_traffic_display_inc")
	hive --database gmall -e "dwd_traffic_display_inc"
	;;
 "dwd_examine_test_exam_inc")
	hive --database gmall -e "dwd_examine_test_exam_inc"
	;;
"all")
	hive --database gmall -e "$dwd_trade_pay_detail_suc_inc$dwd_traffic_action_inc$dwd_paly_video_inc$dwd_paly_course_inc$dwd_trade_order_detail_inc$dwd_traffic_start_inc$dwd_interaction_review_info_inc$dwd_traffic_err_inc$dwd_trade_cart_add_inc$dwd_traffic_display_inc$dwd_examine_test_exam_inc"
	;;
esac